Tourism jobs data layer preparation

National Ocean Economics Program (NOEP)

Downloaded: Manually downloaded by state from website on May 9, 2019.
Description: Total number of jobs and wages per sector for RI, ME, MA, CT, NY and NH counties from 2005 to 2016. The data also include number of establishments and GDP for each sector - state - year.
Native data resolution: County level
Time range: 2005 - 2016
Format: Tabular

NOTES

The data was initially cleaned in the clean_noep_data.R script.

Meta-analysis

To identify some inconsistencies I see in the data, I’m going to take a look at the reported Tourism & Recreation employment at both the county level and statewide. One would expect that the sum of the county employment values would equal what is reported for the “Statewide” employment values. It seems that this is not the case.

states <-  c("Maine", "New Hampshire", "Rhode Island", "Massachusetts", "Connecticut", "New York")

meta <- function(state){
  
  all <- noep_data %>%
    filter(State == !!state,
           str_detect(County, "All")) %>%
    select(Year, Employment) %>%
    distinct() %>%
    rename(all_ctys_tr = Employment)
  
  out <- noep_data %>%
    filter(State == !!state,
           str_detect(County, "All") == FALSE) %>%
    select(State, County, Year, Employment) %>%
    distinct() %>%
    group_by(Year) %>%
    summarize(totals = sum(Employment, na.rm = T)) %>%
    left_join(all) %>%
    rename(county_totals = totals,
           statewide = all_ctys_tr) %>%
    gather(key = spatial_res, value = Employment, -Year) %>%
    mutate(State  = !!state)
  
  return(out)
}

t <- map_df(states, meta) %>%
  distinct()

ggplot(t, aes(x = Year, y = Employment, color = spatial_res)) +
  geom_line() +
  theme_bw() +
  facet_wrap(~State, scales = "free") +
  scale_color_manual(" ", labels = c("County", "State"), values = c("blue", "red")) 

As with the jobs data layer created for Livelihoods, we see clear discrepancies in the dataset between the total number of jobs reported at the state level (red lines) and the sum of all employment numbers at the County level (blue lines) even when filtered just to the Tourism & Recreation sector. Massachusetts shows near-parallel trends in both county and statewide jobs so I am comfortable using the county level data. Since Massachusetts is split into two regions for this assessment, we will need to keep the county resolution of this data. New Hampshire and New York have identical time series so we can safely use the State data there. Rhode Island and Maine show low employment numbers in earlier years when adding up at the county level. This could be due to a lack of data. For example, Saghadoc county in Maine has no data up until 2010, when the jump happens. This suggests we should use the statewide data for Maine and Rhode Island.

There is a weird gap in the Connecticut State time series. I would like to use the State level information for Connecticut, but I will have to gap fill the NA from 2007.

Gapfilling Connecticut data

Gapfilling Connecticut’s statewide TR data. I’m going to do a simple linear interpolation between the years 2007 and 2009. Since the trend is increasing over the entire time series, I am comfortable using this simplified approach.

ct_tr <- noep_data %>% 
  filter(County == "All Connecticut counties") %>%
  mutate(Employment = na.approx(Employment))

Now add back in to the noep_data set.

tr_data <- noep_data %>%
  filter(County != "All Connecticut counties") %>%
  rbind(ct_tr)

Assign spatial scale to use

Using the information gained from the meta analysis above, I’m assigning which spatial scale to use for each of the six states. For all states except MA we are going to use the State level data, which means we filter for rows that say “All [state] counties” and remove the rest. For Massachusetts we want to keep only the individual county information and then summarize total number of jobs from that data.

#select the data for ME, CT, NY, NH, and RI, which is going to use the data reported for "All x counties"
state_data <- tr_data %>%
  filter(str_detect(County, "All"),
         State %in% c("Maine", "Connecticut", "Rhode Island", "New York", "New Hampshire")) %>%
  select(state = State, year = Year, rgn_id, rgn_name, rgn_employment = Employment)
  
#select the data for MA
county_data <- tr_data %>%
  filter(str_detect(County, "All")== FALSE,
         State %in% c("Massachusetts")) %>%
  group_by(rgn_id, Year) %>%
  mutate(rgn_employment = sum(Employment, na.rm = T)) %>% #employment by region
  select(state = State, year = Year, rgn_id, rgn_name, rgn_employment) %>%
  distinct()

tr_data_clean <- bind_rows(state_data, county_data)

ggplot(tr_data_clean, aes(x = year, y = rgn_employment, color = rgn_name)) +
  geom_line() +
  theme_bw() +
  labs(x = "Year",
       y = "Number of jobs",
       color = "Region")

Plot: Employment numbers

Let’s see how the data looks.

c <- tr_jobs %>%
  select(rgn_id, year, rgn_employment, rgn_name, tr_jobs_prev_3yr) %>%
  gather(cat, jobs, -rgn_id, -year, -rgn_name)

ggplot(c, aes(x = year, y = jobs, color = cat)) +
  geom_line() +
  facet_wrap(~rgn_name, scales = "free") +
  ylab("Number of jobs") +
  xlab("Year") +
  theme_bw() +
  ggtitle("Regional employment in the Tourism & Recreation sector") +
  scale_color_manual(" ", labels = c("Annual employment", "Mean employment over the \nprevious 3 years"), values = c("blue", "red")) +
  theme(axis.text.x = element_text(angle = 90),
       strip.text.x = element_text(size = 6))

Plot: Tourism & Recreation sector job growth rate

ggplot(tr_jobs %>% filter(year > 2007), aes(x = year, y = tr_job_growth, color = rgn_name)) +
  geom_line() +
  ylab("Job growth rate") +
  xlab("Year") +
  theme_bw() +
  geom_hline(yintercept = 0) +
  ggtitle("Regional employment in the Tourism & Recreation sector") +
  labs(color = "Region")